package blancosMikel.model.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedList;

import blancosMikel.controller.beans.catalogosBeans;

public class catalogoDAO extends SigaabmDAO{
StringBuilder query = new StringBuilder();
	
	public LinkedList<String> nombreCatalogos() throws SQLException{
		LinkedList<String> lista = new LinkedList<>();
		try {
			conn = this.getConnectionMYSQL();
			query.append("SELECT nombre FROM ").append("catalogos").append(";");
			conn.prepareStatement(query.toString());
			rs = ps.executeQuery();
			while (rs.next()){
				lista.add(rs.getString("nombre"));
			}
		} catch (SQLException sql) {
		}finally{
			rs.close();
			ps.close();
			conn.close();
		}
		return lista;	
	}
	
	public ArrayList<catalogosBeans> consultaTodoCatalogos() throws SQLException{
		ArrayList<catalogosBeans> lista = new ArrayList<catalogosBeans>();
		catalogosBeans bean;
		try {
			conn = this.getConnectionMYSQL();
			query.append("SELECT * FROM ").append("catalogos").append(";");
			conn.prepareStatement(query.toString());
			rs = ps.executeQuery();
			while (rs.next()){
				bean = new catalogosBeans();
				bean.setId_catalogos(rs.getInt("id_catalogos"));
				bean.setId_marca(rs.getInt("id_marca"));
				bean.setNombre(rs.getString("nombre"));
				bean.setUrl(rs.getString("url"));
				lista.add(bean);
			}
		} catch (SQLException sql) {
		}finally{
			rs.close();
			ps.close();
			conn.close();
		}
		return lista;	
	}
	
	public catalogosBeans consultaCatalogo(int id) throws SQLException{
		catalogosBeans bean = null;
		try {
			conn = this.getConnectionMYSQL();
			query.append("SELECT * FROM ").append("catalogos")
			.append(" WHERE id_catalogos =").append(id).append(";");
			conn.prepareStatement(query.toString());
			rs = ps.executeQuery();
			if(rs.next()){
				bean = new catalogosBeans();
				bean.setId_catalogos(rs.getInt("id_catalogos"));
				bean.setId_marca(rs.getInt("id_marca"));
				bean.setNombre(rs.getString("nombre"));
				bean.setUrl(rs.getString("url"));
			}
		} catch (SQLException sql) {
		}finally{
			rs.close();
			ps.close();
			conn.close();
		}
		return bean;
	}
	
	public void eliminarCatalogo(int id) throws SQLException{
		try {
			conn = this.getConnectionMYSQL();
			query.append("DELETE FROM ").append("catalogos")
			.append(" WHERE id_catalogos =").append(id).append(";");
			conn.prepareStatement(query.toString());
			ps.executeUpdate();
	    }catch (SQLException sql) {	    	
	    }finally{
			rs.close();
			ps.close();
			conn.close();
		}
	}
	
	public void insertaCatalogo(catalogosBeans bean) throws SQLException{
		try {
			conn = this.getConnectionMYSQL();
			query.append("INSERT INTO ").append("catalogos")
			.append(" VALUES(").append(bean.getId_catalogos())
			.append(",").append(bean.getId_marca())
			.append(",'").append(bean.getNombre()).append("'")
			.append(",'").append(bean.getUrl()).append("'")
			.append(")").append(";");
			conn.prepareStatement(query.toString());
			ps.executeUpdate();
	    }catch (SQLException sql) {	    	
	    }finally{
			rs.close();
			ps.close();
			conn.close();
		}
	}
}
